﻿CREATE Trigger tr_Address_Insert
On dbo.t_Address
For Insert
As

DECLARE @BorrID int,@Index int,@AddrTypeID int

SELECT @BorrID = BorrowerID,@Index = Pos,@AddrTypeID = AddressTypeID FROM inserted

IF @AddrTypeID = 1 BEGIN

WITH IndexMax(BorrowerID,IndexMax)AS(
SELECT a.BorrowerID,ISNULL(MAX(a.Pos)+1,0) FROM t_Address a
INNER JOIN inserted i ON a.BorrowerID = i.BorrowerID AND a.ID <> i.ID
GROUP BY a.BorrowerID
)
UPDATE t_Address SET Pos = IndexMax.IndexMax
FROM t_Address a
INNER JOIN inserted i ON a.ID = i.ID
INNER JOIN IndexMax ON a.BorrowerID = IndexMax.BorrowerID

/*
	SELECT @Index = MAX(Pos)+1 FROM t_Address WHERE BorrowerID = @BorrID AND AddressTypeID = @AddrTypeID
	UPDATE t_Address SET Pos = ISNULL(@Index,0)
	FROM t_Address a INNER JOIN inserted i ON a.BorrowerID = i.BorrowerID AND a.Pos = i.Pos
*/


END
ELSE
	UPDATE t_Address SET Pos = 10
	FROM t_Address a INNER JOIN inserted i ON a.BorrowerID = i.BorrowerID AND a.Pos = i.Pos
